{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########调包\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import *\n",
    "import time\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "############数据文件文件路径\n",
    "train_dir = '../../contest/train/'\n",
    "test_dir = '../../contest/A榜/'\n",
    "train_pickle_dir = './pickle/train/'\n",
    "test_pickle_dir = './pickle/A/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#取众数\n",
    "def get_mode(series_x):\n",
    "    mode = (series_x.mode())[0]\n",
    "    return mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报1():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报信息表_T0 = pd.read_csv(os.path.join(data_dir,'XW_TAXDECLARE.csv'))    \n",
    "        else:\n",
    "            综合申报信息表_T0 = pd.read_csv(os.path.join(data_dir,'XW_TAXDECLARE_A.csv'))   \n",
    "        综合申报信息表_T0.columns = ['纳税人识别号','申报序号','所属日期起','所属日期止','征收项目名称','申报日期','申报期限','全部销售收入','应税销售收入','应纳税额','减免税额']\n",
    "\t\t\n",
    "        综合申报信息表_T0.sort_values(['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报日期','申报序号'],inplace=True,ascending=True)\n",
    "\t\t\n",
    "        综合申报信息表_T0['全部销售收入'] = pow((综合申报信息表_T0['全部销售收入'])/3.12,3).round(2)\n",
    "        综合申报信息表_T0['应税销售收入'] = pow((综合申报信息表_T0['应税销售收入'])/3.12,3).round(2)\n",
    "        综合申报信息表_T0['应纳税额'] = pow((综合申报信息表_T0['应纳税额'])/3.12,3).round(2)\n",
    "        综合申报信息表_T0['减免税额'] = pow((综合申报信息表_T0['减免税额'])/3.12,3).round(2)\n",
    "\t\t\n",
    "        综合申报信息表_T0['所属日期起'] = 综合申报信息表_T0['所属日期起'].astype('str')\n",
    "        综合申报信息表_T0['所属日期起'] = 综合申报信息表_T0['所属日期起'].astype('datetime64[ns]')\n",
    "        综合申报信息表_T0['所属日期止'] = 综合申报信息表_T0['所属日期止'].astype('str')\n",
    "        综合申报信息表_T0['所属日期止'] = 综合申报信息表_T0['所属日期止'].astype('datetime64[ns]')\n",
    "        综合申报信息表_T0['申报日期'] = 综合申报信息表_T0['申报日期'].astype('str')\n",
    "        综合申报信息表_T0['申报日期'] = 综合申报信息表_T0['申报日期'].astype('datetime64[ns]')\n",
    "        综合申报信息表_T0['申报期限'] = 综合申报信息表_T0['申报期限'].astype('str')\n",
    "        综合申报信息表_T0['申报期限'] = 综合申报信息表_T0['申报期限'].astype('datetime64[ns]')\n",
    "\t\t\n",
    "        综合申报信息表_T0['所属日期起']=pd.to_datetime(综合申报信息表_T0['所属日期起'])+pd.DateOffset(days=11886)\n",
    "        综合申报信息表_T0['所属日期止']=pd.to_datetime(综合申报信息表_T0['所属日期止'])+pd.DateOffset(days=11886)\n",
    "        综合申报信息表_T0['申报日期']=pd.to_datetime(综合申报信息表_T0['申报日期'])+pd.DateOffset(days=11886)\n",
    "        综合申报信息表_T0['申报期限']=pd.to_datetime(综合申报信息表_T0['申报期限'])+pd.DateOffset(days=11886)\n",
    "\n",
    "        pickle.dump(综合申报信息表_T0, open(pickle_dir+'综合申报信息表_临时表.p', 'wb'))\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报2():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报信息表_T0 = pickle.load(open(train_pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报信息表_T0 = pickle.load(open(test_pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "        综合申报_tmp=综合申报信息表_T0.groupby(['纳税人识别号','征收项目名称','所属日期起','所属日期止']).agg({'申报日期':['max']})\n",
    "        综合申报_tmp.reset_index(inplace=True)\n",
    "        综合申报_tmp.columns = ['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报日期']\n",
    "        \n",
    "        综合申报_T1=综合申报信息表_T0.merge(综合申报_tmp,on=['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报日期'],how='inner')\n",
    "        \n",
    "        综合申报_tmp2=综合申报_T1.groupby(['纳税人识别号','征收项目名称','所属日期起','所属日期止']).agg({'申报序号':['max']})\n",
    "        综合申报_tmp2.reset_index(inplace=True)\n",
    "        综合申报_tmp2.columns = ['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报序号']\n",
    "        综合申报_T3=综合申报_T1.merge(综合申报_tmp2,on=['纳税人识别号','征收项目名称','所属日期起','所属日期止','申报序号'],how='inner')\n",
    "        \n",
    "        综合申报_T3['申报年起']=pd.DatetimeIndex(综合申报_T3['所属日期起']).year\n",
    "        综合申报_T3['申报月起']=pd.DatetimeIndex(综合申报_T3['所属日期起']).month\n",
    "        综合申报_T3['申报年止']=pd.DatetimeIndex(综合申报_T3['所属日期止']).year\n",
    "        综合申报_T3['申报月止']=pd.DatetimeIndex(综合申报_T3['所属日期止']).month\n",
    "\n",
    "        综合申报_T3['提前申报时间']= 综合申报_T3.apply(lambda x:(x['申报期限']-x['申报日期']).days, axis=1)\n",
    "        综合申报_T3['要求申报时间']= 综合申报_T3.apply(lambda x:(x['申报期限']-x['所属日期止']).days, axis=1)\n",
    "        综合申报_T3['晚申报标识']=np.where((综合申报_T3['提前申报时间'] < 0),1,0)\n",
    "        综合申报_T3['早申报标识']=np.where(((综合申报_T3['提前申报时间'] -综合申报_T3['提前申报时间']) > 0),1,0)\n",
    "\n",
    "        pickle.dump(综合申报_T3, open(pickle_dir+'综合申报.p', 'wb'))\n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报3():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(train_pickle_dir+'综合申报.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(test_pickle_dir+'综合申报.p', 'rb'))\n",
    "\n",
    "        综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['增值税','企业所得税']))].index, inplace=True)\n",
    "\n",
    "        综合申报_T0.drop(['提前申报时间','要求申报时间','晚申报标识','早申报标识'],axis=1,inplace=True)\n",
    "\n",
    "        综合申报_T1= 综合申报_T0.loc[综合申报_T0['申报年起'] >=2018]\n",
    "        综合申报_T1= 综合申报_T1.loc[综合申报_T1['申报年起'] <=2020]\n",
    "        综合申报_T1= 综合申报_T1.loc[综合申报_T1['申报年起']==综合申报_T1['申报年止']]\n",
    "\n",
    "        所得税_T0= 综合申报_T1.loc[综合申报_T1['征收项目名称'] == '企业所得税']\n",
    "\n",
    "        所得税_tmp=所得税_T0.groupby(['纳税人识别号','所属日期止']).agg({'申报日期':['max']})\n",
    "        所得税_tmp.reset_index(inplace=True)\n",
    "        所得税_tmp.columns = ['纳税人识别号','所属日期止','申报日期']\n",
    "\n",
    "        所得税_T1=所得税_T0.merge(所得税_tmp,on=['纳税人识别号','所属日期止','申报日期'],how='inner')\n",
    "        \n",
    "        所得税_tmp2=所得税_T1.groupby(['纳税人识别号','所属日期止']).agg({'申报序号':['max']})\n",
    "        所得税_tmp2.reset_index(inplace=True)\n",
    "        所得税_tmp2.columns = ['纳税人识别号','所属日期止','申报序号']\n",
    "        \n",
    "        所得税_T2=所得税_T1.merge(所得税_tmp2,on=['纳税人识别号','所属日期止','申报序号'],how='inner')\n",
    "\n",
    "        所得税_tmp3=所得税_T2.groupby(['纳税人识别号','申报年起']).agg({'申报日期':['max']})\n",
    "        所得税_tmp3.reset_index(inplace=True)\n",
    "        所得税_tmp3.columns = ['纳税人识别号','申报年起','申报日期']\n",
    "\n",
    "        所得税_T3=所得税_T2.merge(所得税_tmp3,on=['纳税人识别号','申报年起','申报日期'],how='inner')\n",
    "\n",
    "        所得税_tmp4=所得税_T3.groupby(['纳税人识别号','申报年起']).agg({'所属日期止':['max']})\n",
    "        所得税_tmp4.reset_index(inplace=True)\n",
    "        所得税_tmp4.columns = ['纳税人识别号','申报年起','所属日期止']\n",
    "\t\t\n",
    "        所得税_T4=所得税_T3.merge(所得税_tmp4,on=['纳税人识别号','申报年起','所属日期止'],how='inner')\n",
    "        所得税_T4.drop(['申报序号','所属日期起','所属日期止','征收项目名称','申报日期'\\\n",
    "\t\t,'申报期限','申报月起','申报年止','申报月止'],axis=1,inplace=True)\n",
    "        所得税_T4.columns = ['纳税人识别号','所得税_年收入','所得税_应税收入','所得税_应纳税额','所得税_减免税额','申报年起']\n",
    "\t\t\n",
    "\n",
    "        增值税_T0= 综合申报_T1.loc[综合申报_T1['征收项目名称'] == '增值税']\n",
    "        增值税_T0['申报期窗口']= 增值税_T0.apply(lambda x:(x['所属日期止']-x['所属日期起']).days, axis=1)\n",
    "\t\t\n",
    "        增值税_按年汇总_T1=增值税_T0.groupby(['纳税人识别号','申报年起']).agg({'全部销售收入':['sum'],'应税销售收入':['sum']\\\n",
    "\t\t,'应纳税额':['sum'],'减免税额':['sum'],'申报期窗口':[get_mode,'count']})\n",
    "        增值税_按年汇总_T1.reset_index(inplace=True)\n",
    "        增值税_按年汇总_T1.columns = ['纳税人识别号','申报年起','增值税_年收入','增值税_应税收入','增值税_应纳税额'\\\n",
    "\t\t,'增值税_减免税额','增值税_申报期窗口','增值税_期数']\n",
    "\n",
    "        综合纳税特征_T0=增值税_按年汇总_T1.merge(所得税_T4,on=['纳税人识别号','申报年起'],how='outer')\n",
    "        \n",
    "        pickle.dump(综合纳税特征_T0, open(pickle_dir+'增长纳税按年临时表.p', 'wb'))\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(train_pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(test_pickle_dir+'综合申报信息表_临时表.p', 'rb'))\n",
    "\t\t\t\n",
    "        综合申报_T1=综合申报_T0.groupby(['纳税人识别号','征收项目名称']).agg({'所属日期起':['max','min']})\n",
    "        综合申报_T1.reset_index(inplace=True)\n",
    "        综合申报_T1.columns = ['纳税人识别号','征收项目名称','所属日期起_max','所属日期起_min']\n",
    "\t\t\n",
    "        综合申报_T2=综合申报_T1.groupby(['纳税人识别号']).agg({'所属日期起_min':['min']})\n",
    "        综合申报_T2.reset_index(inplace=True)\n",
    "        综合申报_T2.columns = ['纳税人识别号','最早申报日']\n",
    "        综合申报_T2['最大日期']='2021-03-31'\n",
    "        综合申报_T2['最大日期'] = 综合申报_T2['最大日期'].astype('datetime64[ns]')\n",
    "        综合申报_T2['最早申报日'] = 综合申报_T2['最早申报日'].astype('datetime64[ns]')\n",
    "        综合申报_T2['企业建立日']= 综合申报_T2.apply(lambda x:(x['最大日期']-x['最早申报日']).days, axis=1)\n",
    "        综合申报_T2['企业建立月']= 综合申报_T2['企业建立日']//30\n",
    "        综合申报_T2.drop(['企业建立日','最大日期','最早申报日'],axis=1,inplace=True)\n",
    "\t\t\n",
    "        综合申报_T1.drop(综合申报_T1[~(综合申报_T1.征收项目名称.isin(['增值税','企业所得税']))].index, inplace=True)\n",
    "        综合申报_T3=综合申报_T1.groupby(['纳税人识别号']).agg({'所属日期起_max':['max']})\n",
    "        综合申报_T3.reset_index(inplace=True)\n",
    "        综合申报_T3.columns = ['纳税人识别号','最近申报日']\n",
    "\t\t\n",
    "        综合申报_T3['最大日期']='2021-03-31'\n",
    "        综合申报_T3['最大日期'] = 综合申报_T3['最大日期'].astype('datetime64[ns]')\n",
    "        综合申报_T3['最近申报日'] = 综合申报_T3['最近申报日'].astype('datetime64[ns]')\n",
    "        综合申报_T3['企业最近申报日']= 综合申报_T3.apply(lambda x:(x['最大日期']-x['最近申报日']).days, axis=1)\n",
    "        综合申报_T3['企业最近申报月']= 综合申报_T3['企业最近申报日']//30\n",
    "        综合申报_T3.drop(['企业最近申报日','最大日期','最近申报日'],axis=1,inplace=True)\n",
    "\t\t\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(test_dir,'XW_TARGET_A.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报补充=目标客户列表.merge(综合申报_T2,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充=综合申报补充.merge(综合申报_T3,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(综合申报补充, open(pickle_dir+'综合申报补充.p', 'wb'))\n",
    "        res.append(综合申报补充)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报特征():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(train_pickle_dir+'增长纳税按年临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(test_pickle_dir+'增长纳税按年临时表.p', 'rb'))\n",
    "        综合申报_T0['合并年收入']=np.where(综合申报_T0['增值税_年收入'].isnull()==True,综合申报_T0['所得税_年收入'],综合申报_T0['增值税_年收入'])\n",
    "        综合申报_T0['合并年收入']=np.where(((综合申报_T0['增值税_年收入'].isnull()==False) &(综合申报_T0['所得税_年收入'].isnull()==False)),综合申报_T0[['增值税_年收入','所得税_年收入']].max(axis=1),综合申报_T0['合并年收入'])\n",
    "\n",
    "        综合申报_T0['合并收入年比']=(综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].shift(1)+0.1)).round(0)\n",
    "        综合申报_T0['合并收入2年比']=(综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].diff(2)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['合并年收入'].shift(2)+0.1)).round(0)\n",
    "\t\t\n",
    "        综合申报_T0['合并收入上年比']=综合申报_T0.groupby(['纳税人识别号'])['合并收入年比'].shift(1)\n",
    "\n",
    "        综合申报_T0['增值税_年收入年比']=(综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].shift(1)+0.1)).round(0)\n",
    "        综合申报_T0['增值税_年收入2年比']=(综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].diff(2)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入'].shift(2)+0.1)).round(0)\n",
    "\t\t\n",
    "        综合申报_T0['增值税_年收入上年比']=综合申报_T0.groupby(['纳税人识别号'])['增值税_年收入年比'].shift(1)\n",
    "\n",
    "        综合申报_T0['所得税_年收入年比']=(综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].shift(1)+0.1)).round(0)\n",
    "        综合申报_T0['所得税_年收入2年比']=(综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].diff(2)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入'].shift(2)+0.1)).round(0)\n",
    "\t\t\n",
    "        综合申报_T0['所得税_年收入上年比']=综合申报_T0.groupby(['纳税人识别号'])['所得税_年收入年比'].shift(1)\n",
    "\t\t\n",
    "        综合申报_T0['增值税_税率']=(100*综合申报_T0['增值税_应纳税额']/综合申报_T0['增值税_应税收入']).round(0)\n",
    "        综合申报_T0['所得税_税率']=(100*综合申报_T0['所得税_应纳税额']/综合申报_T0['所得税_应税收入']).round(0)\n",
    "\n",
    "        综合申报_T0['增值税减免比']=(100*综合申报_T0['增值税_减免税额']/综合申报_T0['增值税_应纳税额']).round(0)\n",
    "        综合申报_T0['所得税减免比']=(100*综合申报_T0['所得税_减免税额']/综合申报_T0['所得税_应纳税额']).round(0)\n",
    "\n",
    "        综合申报_T0['增值税_应纳税额年比']=(综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].shift(1)+0.1)).round(0)\n",
    "        综合申报_T0['增值税_应纳税额2年比']=(综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].diff(2)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额'].shift(2)+0.1)).round(0)\n",
    "\t\t\n",
    "        综合申报_T0['增值税_应纳税额上年比']=综合申报_T0.groupby(['纳税人识别号'])['增值税_应纳税额年比'].shift(1)\n",
    "\n",
    "        综合申报_T0['所得税_应纳税额年比']=(综合申报_T0.groupby(['纳税人识别号'])['所得税_减免税额'].diff(1)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['所得税_减免税额'].shift(1)+0.1)).round(0)\n",
    "        综合申报_T0['所得税_应纳税额2年比']=(综合申报_T0.groupby(['纳税人识别号'])['所得税_减免税额'].diff(2)*100/\\\n",
    "\t\t(综合申报_T0.groupby(['纳税人识别号'])['所得税_减免税额'].shift(2)+0.1)).round(0)\n",
    "\t\t\n",
    "        综合申报_T0['所得税_应纳税额上年比']=综合申报_T0.groupby(['纳税人识别号'])['所得税_应纳税额年比'].shift(1)\n",
    "\t\t\n",
    "        综合申报_T0['增值税_税率'].fillna(综合申报_T0.groupby(['纳税人识别号'])['增值税_税率'].shift(1),inplace=True)\n",
    "        综合申报_T0['所得税_税率'].fillna(综合申报_T0.groupby(['纳税人识别号'])['所得税_税率'].shift(1),inplace=True)\n",
    "        综合申报_T0['所得税减免比'].fillna(综合申报_T0.groupby(['纳税人识别号'])['所得税减免比'].shift(1),inplace=True)\n",
    "        综合申报_T0['增值税减免比'].fillna(综合申报_T0.groupby(['纳税人识别号'])['增值税减免比'].shift(1),inplace=True)\n",
    "\t\t\n",
    "        综合申报_T1=综合申报_T0.groupby(['纳税人识别号']).agg({'合并年收入':['sum','last'],'增值税_年收入':['sum','last']\\\n",
    "\t\t,'所得税_年收入':['sum','last'],'增值税_应纳税额':['sum'],'所得税_应纳税额':['sum'],'增值税_税率':['last']\\\n",
    "\t\t,'所得税_税率':['last'],'所得税减免比':['last'],'增值税减免比':['last']\\\n",
    "\t\t,'合并收入年比':['last'],'合并收入上年比':['last'],'合并收入2年比':['last'],'增值税_应纳税额年比':['last'],'增值税_应纳税额上年比':['last'],'增值税_应纳税额2年比':['last']\\\n",
    "\t\t,'所得税_应纳税额年比':['last'],'所得税_应纳税额上年比':['last'],'所得税_应纳税额2年比':['last'],'增值税_期数':['last','std'],'增值税_申报期窗口':['last']})\n",
    "        result=[]\n",
    "        for col in 综合申报_T1.columns.values:\n",
    "            tmp= col[0]+'_'+col[1]\n",
    "            result.append(tmp)\n",
    "        综合申报_T1.columns = result\n",
    "        综合申报_T1.reset_index(inplace=True)\n",
    "\t\t\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(test_dir,'XW_TARGET_A.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报_T1=目标客户列表.merge(综合申报_T1,on=['纳税人识别号'],how='left')\n",
    "        综合申报_T1.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(综合申报_T1, open(pickle_dir+'申报特征.p', 'wb'))\n",
    "        res.append(综合申报_T1)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充2():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(train_pickle_dir+'综合申报.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(test_pickle_dir+'综合申报.p', 'rb'))\n",
    "\n",
    "        综合申报_T0.drop(['晚申报标识','早申报标识'],axis=1,inplace=True)\n",
    "        \n",
    "        综合申报_T1= 综合申报_T0.loc[综合申报_T0['申报年起'] >=2018]\n",
    "        综合申报_T1.sort_values(['纳税人识别号','征收项目名称','申报日期'],inplace=True,ascending=True)\n",
    "\n",
    "        综合申报_T1['上期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(1)\n",
    "        综合申报_T1['下期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(-1)\n",
    "\n",
    "        综合申报_T2= 综合申报_T1.loc[~((综合申报_T1['上期申报年起'] ==综合申报_T1['下期申报年起']) \\\n",
    "\t\t& (综合申报_T1['申报年起']<综合申报_T1['上期申报年起']))]\n",
    "\n",
    "        综合申报_T2['上期所属日期止']=综合申报_T2.groupby(['纳税人识别号','征收项目名称'])['所属日期止'].shift(1)\n",
    "        综合申报_T2['申报间隔']= 综合申报_T2.apply(lambda x:(x['所属日期起']-x['上期所属日期止']).days, axis=1)\n",
    "\n",
    "        pickle.dump(综合申报_T2, open(pickle_dir+'综合补充_临时表.p', 'wb'))\n",
    "    return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工综合申报补充3():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(test_dir,test_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            综合申报_T0 = pickle.load(open(train_pickle_dir+'综合补充_临时表.p', 'rb'))\n",
    "        else:\n",
    "            综合申报_T0 = pickle.load(open(test_pickle_dir+'综合补充_临时表.p', 'rb'))\n",
    "\n",
    "        综合申报_T1= 综合申报_T0.loc[综合申报_T0['申报间隔'] >-300]\n",
    "        综合申报_T1= 综合申报_T1.loc[综合申报_T1['申报间隔'] !=1]\n",
    "\n",
    "        综合申报_T2=综合申报_T1.groupby(['纳税人识别号','征收项目名称','申报年起']).agg({'申报间隔':['max','count','sum']})\n",
    "        综合申报_T2.reset_index(inplace=True)\n",
    "        综合申报_T2.columns = ['纳税人识别号','征收项目名称','申报年起','最长漏报天','漏报次数','漏报天_sum']\n",
    "\n",
    "        综合申报_T3= 综合申报_T2.drop(综合申报_T2[~(综合申报_T2.征收项目名称.isin(['企业所得税','增值税']))].index)\n",
    "\n",
    "        综合申报_T4=综合申报_T3.groupby(['纳税人识别号']).agg({'最长漏报天':['max'],'漏报次数':['sum','mean'],'漏报天_sum':['sum']})\n",
    "        综合申报_T4.reset_index(inplace=True)\n",
    "        综合申报_T4.columns = ['纳税人识别号','最长漏报天','漏报次数','漏报次数_mean','漏报天_sum']\n",
    "\n",
    "        综合申报_T5=综合申报_T0.drop(综合申报_T0[~(综合申报_T0.征收项目名称.isin(['企业所得税']))].index)\n",
    "\n",
    "        综合申报_T6=综合申报_T5.groupby(['纳税人识别号']).agg({'提前申报时间':['mean','std'],'要求申报时间':['max']})\n",
    "        综合申报_T6.reset_index(inplace=True)\n",
    "        综合申报_T6.columns = ['纳税人识别号','提前申报时间_mean','提前申报时间_std','要求申报时间_max']\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(test_dir,'XW_TARGET_A.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        综合申报补充=目标客户列表.merge(综合申报_T4,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充=综合申报补充.merge(综合申报_T6,on=['纳税人识别号'],how='left')\n",
    "        综合申报补充.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(综合申报补充, open(pickle_dir+'综合申报补充2.p', 'wb'))\n",
    "        res.append(综合申报补充)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "加工综合申报1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "加工综合申报2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-23-8cbffdbf5240>:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  增值税_T0['申报期窗口']= 增值税_T0.apply(lambda x:(x['所属日期止']-x['所属日期起']).days, axis=1)\n",
      "<ipython-input-23-8cbffdbf5240>:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  增值税_T0['申报期窗口']= 增值税_T0.apply(lambda x:(x['所属日期止']-x['所属日期起']).days, axis=1)\n"
     ]
    }
   ],
   "source": [
    "加工综合申报3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "综合申报补充_训练集,综合申报补充_测试集=加工综合申报补充()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "申报特征_训练集,申报特征_测试集=加工综合申报特征()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-26-9f237e0ca014>:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1.sort_values(['纳税人识别号','征收项目名称','申报日期'],inplace=True,ascending=True)\n",
      "<ipython-input-26-9f237e0ca014>:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['上期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(1)\n",
      "<ipython-input-26-9f237e0ca014>:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['下期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(-1)\n",
      "<ipython-input-26-9f237e0ca014>:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['上期所属日期止']=综合申报_T2.groupby(['纳税人识别号','征收项目名称'])['所属日期止'].shift(1)\n",
      "<ipython-input-26-9f237e0ca014>:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['申报间隔']= 综合申报_T2.apply(lambda x:(x['所属日期起']-x['上期所属日期止']).days, axis=1)\n",
      "<ipython-input-26-9f237e0ca014>:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1.sort_values(['纳税人识别号','征收项目名称','申报日期'],inplace=True,ascending=True)\n",
      "<ipython-input-26-9f237e0ca014>:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['上期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(1)\n",
      "<ipython-input-26-9f237e0ca014>:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T1['下期申报年起']=综合申报_T1.groupby(['纳税人识别号','征收项目名称'])['申报年起'].shift(-1)\n",
      "<ipython-input-26-9f237e0ca014>:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['上期所属日期止']=综合申报_T2.groupby(['纳税人识别号','征收项目名称'])['所属日期止'].shift(1)\n",
      "<ipython-input-26-9f237e0ca014>:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  综合申报_T2['申报间隔']= 综合申报_T2.apply(lambda x:(x['所属日期起']-x['上期所属日期止']).days, axis=1)\n"
     ]
    }
   ],
   "source": [
    "加工综合申报补充2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "申报补充2_训练集,申报补充2_测试集=加工综合申报补充3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
